const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🔍 检查实例表字段与三个参数表的映射准确性');
console.log('===============================================');

try {
  const db = new Database(DB_PATH);
  
  console.log('\n📊 获取核心参数表结构...');
  
  // 1. 获取三个核心参数表的完整字段信息
  const coreTableFields = {};
  const coreTables = ['table_c2', 'table_c4', 'table_c6'];
  
  for (const tableName of coreTables) {
    try {
      const fields = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      coreTableFields[tableName] = fields.filter(f => 
        !['id', 'created_at', 'updated_at'].includes(f.name)
      );
      
      console.log(`📋 ${tableName}: ${coreTableFields[tableName].length} 个字段`);
      coreTableFields[tableName].forEach(field => {
        console.log(`  - ${field.name} (${field.type})`);
      });
    } catch (error) {
      console.log(`❌ 无法获取 ${tableName}: ${error.message}`);
      coreTableFields[tableName] = [];
    }
  }
  
  console.log('\n🔍 获取设备类型映射关系...');
  
  // 2. 获取设备类型映射，了解每个设备类型使用哪些参数表
  const deviceTypeMappings = db.prepare(`
    SELECT 
      classification_code,
      classification_name,
      english_name,
      specialty,
      naming_table,
      position_table,
      base_params_table
    FROM device_type_mappings
    WHERE english_name IS NOT NULL
    ORDER BY specialty, english_name
  `).all();
  
  console.log(`📊 设备类型映射: ${deviceTypeMappings.length} 个`);
  
  // 3. 获取所有实例表
  const instanceTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`📋 实例表总数: ${instanceTables.length} 个`);
  
  console.log('\n🔧 分析字段映射准确性...');
  console.log('=' * 80);
  
  let totalCorrectMappings = 0;
  let totalIncorrectMappings = 0;
  let totalMissingFields = 0;
  let totalExtraFields = 0;
  const mappingErrors = [];
  
  // 4. 逐个检查每个实例表的字段映射
  for (const table of instanceTables) {
    const tableName = table.name;
    
    try {
      console.log(`\n🔍 检查: ${tableName}`);
      
      // 提取设备类型（去掉前缀）
      const deviceType = tableName.replace('device_instances_', '');
      
      // 查找对应的设备类型映射
      let mapping = deviceTypeMappings.find(m => 
        m.english_name && m.english_name.toLowerCase() === deviceType.toLowerCase()
      );
      
      if (!mapping) {
        // 尝试部分匹配
        mapping = deviceTypeMappings.find(m => {
          if (!m.english_name) return false;
          const englishParts = m.english_name.toLowerCase().split('_');
          const deviceParts = deviceType.toLowerCase().split('_');
          return englishParts.some(part => deviceParts.includes(part)) ||
                 deviceParts.some(part => englishParts.includes(part));
        });
      }
      
      if (!mapping) {
        console.log(`  ⚠️  未找到对应的设备类型映射`);
        mappingErrors.push({
          table: tableName,
          error: 'No mapping found',
          deviceType: deviceType
        });
        continue;
      }
      
      console.log(`  📋 映射到: ${mapping.classification_name} (${mapping.classification_code})`);
      console.log(`  📊 参数表组合: ${mapping.naming_table} + ${mapping.position_table} + ${mapping.base_params_table}`);
      
      // 获取实例表的实际字段
      const actualFields = db.prepare(`PRAGMA table_info([${tableName}])`).all();
      const actualNamingFields = actualFields.filter(f => f.name.startsWith('naming_'));
      const actualPositionFields = actualFields.filter(f => f.name.startsWith('position_'));
      const actualBaseParamsFields = actualFields.filter(f => f.name.startsWith('base_params_'));
      
      console.log(`  📊 实际字段: 命名(${actualNamingFields.length}) + 位置(${actualPositionFields.length}) + 参数(${actualBaseParamsFields.length})`);
      
      // 5. 检查命名字段映射 (table_c2)
      console.log(`\n  🔍 检查命名字段映射 (${mapping.naming_table}):`);
      const expectedNamingFields = coreTableFields[mapping.naming_table] || [];
      
      let namingCorrect = 0;
      let namingMissing = 0;
      let namingExtra = 0;
      
      // 检查期望的字段是否都存在
      for (const expectedField of expectedNamingFields) {
        const expectedName = `naming_${expectedField.name}`;
        const actualField = actualNamingFields.find(f => f.name === expectedName);
        
        if (actualField) {
          console.log(`    ✅ ${expectedName} (${expectedField.type} -> ${actualField.type})`);
          namingCorrect++;
        } else {
          console.log(`    ❌ 缺失: ${expectedName} (${expectedField.type})`);
          namingMissing++;
          mappingErrors.push({
            table: tableName,
            error: 'Missing naming field',
            field: expectedName,
            expectedType: expectedField.type
          });
        }
      }
      
      // 检查是否有多余的字段
      for (const actualField of actualNamingFields) {
        const expectedName = actualField.name.replace('naming_', '');
        const expected = expectedNamingFields.find(f => f.name === expectedName);
        
        if (!expected) {
          console.log(`    ⚠️  多余: ${actualField.name}`);
          namingExtra++;
        }
      }
      
      // 6. 检查位置字段映射 (table_c4 或 table_c6)
      let positionCorrect = 0;
      let positionMissing = 0;
      let positionExtra = 0;
      
      if (mapping.position_table && mapping.position_table !== '-' && mapping.position_table !== 'null') {
        console.log(`\n  🔍 检查位置字段映射 (${mapping.position_table}):`);
        const expectedPositionFields = coreTableFields[mapping.position_table] || [];
        
        for (const expectedField of expectedPositionFields) {
          const expectedName = `position_${expectedField.name}`;
          const actualField = actualPositionFields.find(f => f.name === expectedName);
          
          if (actualField) {
            console.log(`    ✅ ${expectedName} (${expectedField.type} -> ${actualField.type})`);
            positionCorrect++;
          } else {
            console.log(`    ❌ 缺失: ${expectedName} (${expectedField.type})`);
            positionMissing++;
            mappingErrors.push({
              table: tableName,
              error: 'Missing position field',
              field: expectedName,
              expectedType: expectedField.type
            });
          }
        }
        
        for (const actualField of actualPositionFields) {
          const expectedName = actualField.name.replace('position_', '');
          const expected = expectedPositionFields.find(f => f.name === expectedName);
          
          if (!expected) {
            console.log(`    ⚠️  多余: ${actualField.name}`);
            positionExtra++;
          }
        }
        
        totalCorrectMappings += positionCorrect;
        totalMissingFields += positionMissing;
        totalExtraFields += positionExtra;
      } else {
        console.log(`\n  ℹ️  该设备类型无位置字段要求`);
      }
      
      // 7. 检查基础参数字段映射
      let baseParamsCorrect = 0;
      let baseParamsMissing = 0;
      let baseParamsExtra = 0;
      
      if (mapping.base_params_table && mapping.base_params_table !== '-' && mapping.base_params_table !== 'null') {
        console.log(`\n  🔍 检查基础参数字段映射 (${mapping.base_params_table}):`);
        
        try {
          const baseParamsFields = db.prepare(`PRAGMA table_info([${mapping.base_params_table}])`).all();
          const expectedBaseParamsFields = baseParamsFields.filter(f => 
            !['id', 'created_at', 'updated_at'].includes(f.name)
          );
          
          for (const expectedField of expectedBaseParamsFields) {
            const expectedName = `base_params_${expectedField.name}`;
            const actualField = actualBaseParamsFields.find(f => f.name === expectedName);
            
            if (actualField) {
              console.log(`    ✅ ${expectedName} (${expectedField.type} -> ${actualField.type})`);
              baseParamsCorrect++;
            } else {
              console.log(`    ❌ 缺失: ${expectedName} (${expectedField.type})`);
              baseParamsMissing++;
              mappingErrors.push({
                table: tableName,
                error: 'Missing base_params field',
                field: expectedName,
                expectedType: expectedField.type
              });
            }
          }
          
          for (const actualField of actualBaseParamsFields) {
            const expectedName = actualField.name.replace('base_params_', '');
            const expected = expectedBaseParamsFields.find(f => f.name === expectedName);
            
            if (!expected) {
              console.log(`    ⚠️  多余: ${actualField.name}`);
              baseParamsExtra++;
            }
          }
          
          // 累计统计在这里进行
          
        } catch (error) {
          console.log(`    ❌ 无法读取基础参数表 ${mapping.base_params_table}: ${error.message}`);
          mappingErrors.push({
            table: tableName,
            error: 'Cannot read base_params table',
            baseParamsTable: mapping.base_params_table,
            message: error.message
          });
        }
      } else {
        console.log(`\n  ℹ️  该设备类型无基础参数字段要求`);
      }
      
      // 累计统计
      totalCorrectMappings += namingCorrect + positionCorrect + baseParamsCorrect;
      totalMissingFields += namingMissing + positionMissing + baseParamsMissing;
      totalExtraFields += namingExtra + positionExtra + baseParamsExtra;
      
      // 显示该表的映射总结
      const tableTotal = namingCorrect + positionCorrect + baseParamsCorrect;
      const tableMissing = namingMissing + positionMissing + baseParamsMissing;
      const tableExtra = namingExtra + positionExtra + baseParamsExtra;
      
      console.log(`\n  📊 该表映射总结: ✅${tableTotal} | ❌${tableMissing} | ⚠️${tableExtra}`);
      
      if (tableMissing === 0 && tableExtra === 0) {
        console.log(`  🎉 完美映射！`);
      } else if (tableMissing > 0) {
        console.log(`  ⚠️  存在映射问题`);
        totalIncorrectMappings++;
      }
      
    } catch (error) {
      console.log(`  ❌ 检查失败: ${error.message}`);
      mappingErrors.push({
        table: tableName,
        error: 'Check failed',
        message: error.message
      });
    }
  }
  
  // 8. 生成总体报告
  console.log('\n📊 字段映射验证报告');
  console.log('====================');
  console.log(`✅ 正确映射字段: ${totalCorrectMappings} 个`);
  console.log(`❌ 缺失字段: ${totalMissingFields} 个`);
  console.log(`⚠️  多余字段: ${totalExtraFields} 个`);
  console.log(`🚨 存在问题的表: ${totalIncorrectMappings} 个`);
  
  if (mappingErrors.length > 0) {
    console.log(`\n🚨 映射错误详情 (${mappingErrors.length} 个):`);
    
    // 按错误类型分组
    const errorsByType = {};
    mappingErrors.forEach(error => {
      if (!errorsByType[error.error]) {
        errorsByType[error.error] = [];
      }
      errorsByType[error.error].push(error);
    });
    
    Object.entries(errorsByType).forEach(([errorType, errors]) => {
      console.log(`\n❌ ${errorType} (${errors.length} 个):`);
      errors.slice(0, 10).forEach(error => { // 只显示前10个
        if (error.field) {
          console.log(`  - ${error.table}: ${error.field}`);
        } else {
          console.log(`  - ${error.table}: ${error.message || error.baseParamsTable}`);
        }
      });
      if (errors.length > 10) {
        console.log(`  ... 还有 ${errors.length - 10} 个类似错误`);
      }
    });
  }
  
  // 9. 分析映射逻辑
  console.log('\n🔧 映射逻辑分析');
  console.log('================');
  console.log(`📋 核心映射规则:`);
  console.log(`  1. 命名字段: table_c2.字段名 -> naming_字段名`);
  console.log(`  2. 位置字段: table_c4/c6.字段名 -> position_字段名`);
  console.log(`  3. 参数字段: table_c*.字段名 -> base_params_字段名`);
  console.log(`  4. 数据类型: 统一转换为 TEXT 类型`);
  console.log(`  5. 系统字段: id, device_type_code, instance_number, object_number, object_description, created_at, updated_at`);
  
  // 10. 映射准确性评估
  const totalExpectedFields = totalCorrectMappings + totalMissingFields;
  const accuracyRate = totalExpectedFields > 0 ? ((totalCorrectMappings / totalExpectedFields) * 100).toFixed(2) : 100;
  
  console.log(`\n📊 映射准确性评估:`);
  console.log(`  准确率: ${accuracyRate}% (${totalCorrectMappings}/${totalExpectedFields})`);
  
  if (accuracyRate == 100) {
    console.log(`  🎉 完美！所有字段映射都准确无误`);
  } else if (accuracyRate >= 95) {
    console.log(`  ✅ 优秀！映射基本准确，只有少量问题`);
  } else if (accuracyRate >= 80) {
    console.log(`  ⚠️  良好，但存在一些映射问题需要修复`);
  } else {
    console.log(`  ❌ 映射存在较多问题，需要重新检查映射逻辑`);
  }
  
  console.log(`\n💡 映射逻辑总结:`);
  console.log(`  ✅ 使用设备类型映射表 (device_type_mappings) 确定参数表组合`);
  console.log(`  ✅ 通过英文名称 (english_name) 匹配实例表和设备类型`);
  console.log(`  ✅ 三表整合: 命名表 + 位置表 + 基础参数表`);
  console.log(`  ✅ 前缀命名: naming_, position_, base_params_`);
  console.log(`  ✅ 字段过滤: 排除系统字段 (id, created_at, updated_at)`);
  
  db.close();
  
} catch (error) {
  console.error('❌ 验证失败:', error.message);
  console.error(error.stack);
}